package com.rockgle.persistence;

import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.rockgle.reader.CustomApplication;
import com.rockgle.reader.Entry;
import com.rockgle.reader.PreferenceMonitor;
import com.rockgle.reader.SortedEntryList;

public class DAO {
	private final DBOpenHelper mDBOpenHelper;
	
	private static final String TAG = "RockgleDatabase";
	private static final String DATABASE_NAME = "rockgle.db";
	private static final int DATABASE_VERSION = 1;
	private static final String ENTRIES_TABLE = "Entries";
	private static final String PREFERENCES_TABLE = "Preferences";
	private final int ENTRIES_PER_PAGE = 25;
	
	public DAO(){
		mDBOpenHelper = new DBOpenHelper();
	}
	
	public synchronized List<Entry> getAllEntries() {
		String query= "SELECT * FROM Entries ORDER BY published DESC";
		Cursor c = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
		//query(null, null, new String[]{"id","published","title","content"});
        return getEntriesFromCursor(c);

        /* This builds a query that looks like:
         *     SELECT <columns> FROM <table> WHERE rowid = <rowId>
         */
    }
	
	public synchronized boolean isAppFirstRun() {
		String query= "SELECT value FROM "+PREFERENCES_TABLE+" WHERE key='firstRun'";
		Cursor c = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
		c.moveToFirst();
		String cadena = c.getString(0);

        return cadena.equals("true");
    }
	
	public synchronized void toggleAppFirstRun() {
		String sql= "UPDATE "+PREFERENCES_TABLE+" SET value='false' WHERE key='firstRun'";
		mDBOpenHelper.getWritableDatabase().execSQL(sql);
        return;
    }
	
	private synchronized List<Entry> getEntriesFromCursor(Cursor cur){
		SortedEntryList lista = new SortedEntryList();
		
		int idindex = cur.getColumnIndex("id");
		int titleindex = cur.getColumnIndex("title");
		int publishedindex = cur.getColumnIndex("published");
		int contentindex = cur.getColumnIndex("content");
		int readindex = cur.getColumnIndex("read");
		int authorindex = cur.getColumnIndex("author");
    	
    	cur.moveToFirst();
        while (cur.isAfterLast() == false) {
        	Entry entry = new Entry();
        	entry.setId(cur.getLong(idindex));
            entry.setPublishedRFC3339(cur.getString(publishedindex));
            entry.setTitle(cur.getString(titleindex));
            entry.setContent(cur.getString(contentindex));
            entry.setAuthor(cur.getString(authorindex));
            if (cur.getInt(readindex) == 1){
            	entry.markAsRead();
            }
            if (!lista.contains(entry)){
            	lista.insertSorted(entry);
            }
            
       	    cur.moveToNext();
        }
        cur.close();
		return lista;
	}
	
	public synchronized List<Entry> getUnreadEntries(int currentfirstentry){
		String query= "SELECT * FROM Entries WHERE read=0 ORDER BY published DESC LIMIT "
			+String.valueOf(ENTRIES_PER_PAGE)+" OFFSET " +
			String.valueOf(currentfirstentry);

		Cursor c = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
		return getEntriesFromCursor(c);
	}
	
	public synchronized List<Entry> getEntries(int currentfirstentry) {
		String query= "SELECT * FROM Entries ORDER BY published DESC LIMIT "
						+String.valueOf(ENTRIES_PER_PAGE)+" OFFSET " +
						String.valueOf(currentfirstentry);

		Cursor c = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
        return getEntriesFromCursor(c);
    }
	
	/**
	 * Insertar una entrada en persistencia, si la entrada ya existe se modificara
	 * @param entry Objeto a insertaren persistencia
	 * @return -1 si ha habido un error, otro valor en caso contrario
	 */
	public synchronized long addEntry(Entry entry) {
		long retorno;
		
		ContentValues initialValues = new ContentValues();
		initialValues.put("published", entry.getPublishedRFC3339());
        initialValues.put("title", entry.getTitle());
        initialValues.put("content", entry.getContent());
        initialValues.put("author", entry.getAuthor());
        if (entry.isRead()){
        	initialValues.put("read", 1);
        }else{
        	initialValues.put("read", 0);
        }
		
		if (!isEntryStored(entry)){
			initialValues.put("id", entry.getId());
			retorno = mDBOpenHelper.getWritableDatabase().insert(ENTRIES_TABLE, null, initialValues);
		}else{
			String whereClause ="id="+String.valueOf(entry.getId());
			retorno = mDBOpenHelper.getWritableDatabase().update(ENTRIES_TABLE, initialValues, whereClause, null);
		}
        return retorno;
    }
	
	/**	
	 * True si el objeto ya esta almacenado en persistencia
	 * @param entry objeto del que seva a comprobar si esta o no en persistencia
	 * @return
	 */
	public boolean isEntryStored(Entry entry){
		long id = entry.getId();
		String selectquery = "SELECT * FROM Entries WHERE id="+String.valueOf(id);
		Cursor cur = mDBOpenHelper.getReadableDatabase().rawQuery(selectquery, null);
		boolean retorno = cur.getCount()>0;
		cur.close();
		return retorno;
	}
	
	public boolean isEmpty(){
		String query= "SELECT COUNT(*) FROM Entries";
		Cursor c = mDBOpenHelper.getReadableDatabase().rawQuery(query, null);
		c.moveToFirst();
		boolean retorno = c.getInt(0)==0;
		c.close();
		return retorno;
	}
    
    public List<Entry> getMostRecentEntries(int count){    	
    	String query= "SELECT * FROM Entries ORDER BY published DESC LIMIT "+String.valueOf(count);
    	Cursor cur = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
		return getEntriesFromCursor(cur);
    }
    
    public List<Entry> getLeastRecentEntries(int count){
    	String query;
    	if (PreferenceMonitor.getOnlyUnread()){
    		query= "SELECT * FROM Entries WHERE read=0 ORDER BY published ASC LIMIT "+String.valueOf(count);
    	}else{
    		query= "SELECT * FROM Entries ORDER BY published ASC LIMIT "+String.valueOf(count);
    	}
    	Cursor cur = mDBOpenHelper.getReadableDatabase().rawQuery(query,null);
		return getEntriesFromCursor(cur);
    }
	
	private static class DBOpenHelper extends SQLiteOpenHelper{
	    private SQLiteDatabase mDatabase;
		
		private static final String ENTRIES_TABLE_CREATE =
            "CREATE TABLE " + ENTRIES_TABLE +
            " (id INTEGER PRIMARY KEY, published DATETIME, title TEXT, content TEXT, read INTEGER, author TEXT);";
		
		private static final String PREFERENCES_TABLE_CREATE =
            "CREATE TABLE " + PREFERENCES_TABLE +
            " (key TEXT PRIMARY KEY, value TEXT);";
		
		public DBOpenHelper() {
			super(CustomApplication.getContext(), DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			mDatabase = db;
            mDatabase.execSQL(ENTRIES_TABLE_CREATE);
            mDatabase.execSQL(PREFERENCES_TABLE_CREATE);
            mDatabase.execSQL("INSERT INTO "+PREFERENCES_TABLE+" (key,value) VALUES ('firstRun','true')");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + ENTRIES_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + PREFERENCES_TABLE);
            onCreate(db);
		}
	}
}
